package com.lmx.persistence.service;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.lmx.common.constants.CommonConstants;
import com.lmx.common.context.ActionContextHolder;
import com.lmx.common.util.CommonUtil;
import com.lmx.common.util.PropertiesFileUtil;
import com.lmx.common.util.SMSUtil;
import com.lmx.repoDomain.tmodel.Customer;
import com.lmx.repoDomain.tmodel.CustomerBusinessFlow;
import com.lmx.repoDomain.tmodel.CustomerLeave;
import com.lmx.repoDomain.tmodel.CustomerServerChange;
import com.lmx.repoDomain.tmodel.Dicttype;
import com.lmx.repoDomain.tmodel.Employee;
import com.lmx.repoDomain.tmodel.NursingLevel;
import com.lmx.repoDomain.tmodel.NursingPlanDetail;
import com.lmx.repoDomain.tmodel.NursingProject;
import com.lmx.repoDomain.tmodel.PersonLocation;
import com.lmx.repoDomain.tmodel.StaffWorkTimeStat;
import com.lmx.repoDomain.tmodel.StatQuery;
import com.lmx.repoDomain.tmodel.Tenant;
import com.lmx.repoDomain.tmodel.TenantLocalNotify;
import com.lmx.thirdPart.BusinessProcessor;
import com.lmx.thirdPart.ThirdPartBusiException;

/**
 * Created by qin on 2016/7/11.
 */
@Component
public class TimerService {
	private Log logger = LogFactory.getLog(this.getClass());

	@PersistenceContext
	private EntityManager em;

	@Autowired
	private MessageService messageService;

	@Autowired
	private GenericService dslJpaService;

	@Autowired
	private AccountService accountService;

	@Autowired
	private NursingService nursingService;

	@Autowired
	private CustomerService customerService;

	@Autowired
	private CommonService commonService;

	@Autowired
	private DeviceService deviceService;

	@Autowired
	private LocalHbeaconService localHbeaconService;

	@Autowired
	private BusinessProcessor daermaBusinessProcessor;

	@Autowired
	private PersonLocationReportService locationReportSrv;

	@Autowired
	private CustomerLeaveService leaveService;

	@Autowired
	private PersonLocationIOReportService locationIOReporSrv;

	@Autowired
	private EmployeeService employeeService;
	
	@Autowired
	private PersonLocationAnalysisService locationAnalysisService;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	protected JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	private Properties properties = PropertiesFileUtil.getProperties("application.properties");

	/*
	 * @Scheduled(cron = "0 0 9,13,18 * * ?")
	 * 
	 */
	// @Scheduled(cron = "0 0 0 1/1 * ?")

	/**
	 * 生成客户前日 日账单/处理退住审核通过的记录/处理服务变更记录 （每日凌晨一分执行）
	 */
	@Scheduled(cron = "0 1 0 * * ?")
	@Transactional
	public void createCustomerDayBill() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		String dayTime = "";// 昨天日期
		Integer yesTimeStart = null;
		Integer yesTimeEnd = null;
		String curTime = "";// 当日日期
		Integer curTimeStart = null;
		Integer curTimeEnd = null;
		try {
			dayTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			yesTimeStart = CommonUtil.strToIntegerTime(dayTime.substring(0, 10) + " 00:00:00");
			yesTimeEnd = CommonUtil.strToIntegerTime(dayTime.substring(0, 10) + " 23:59:59");
			curTime = CommonUtil.getCurrTimeForString();// 当日日期
			curTimeStart = CommonUtil.strToIntegerTime(curTime.substring(0, 10) + " 00:00:00");
			curTimeEnd = CommonUtil.strToIntegerTime(curTime.substring(0, 10) + " 23:59:59");
		} catch (Exception e) {
			logger.debug("create bill error :" + e);
		}
		List<NursingProject> projects = new ArrayList<>();
		projects = this.em.createQuery("select c from NursingProject c where c.delStatus!=1 ").getResultList();
		Map<Customer, NursingLevel> projectMap = (Map<Customer, NursingLevel>) projects.stream()
				.collect(Collectors.toMap(NursingProject::getCustomer, NursingProject::getLevel, (key1, key2) -> key2));
		// 对费用结算中的退住 进行操作 退住申请---操作为费用结算中
		logger.info("start operateCustomerBack " + CommonUtil.getCurrTimeForString());
		// 处理昨天到期的退住
		// List<CustomerBusinessFlow> flowYList = this.em
		// .createQuery(
		// "select a from CustomerBusinessFlow a where a.delStatus != ?1 and
		// a.leaveStatus.typecode=?2 and a.leaveDate>=?3 and a.leaveDate<=?4 ")
		// .setParameter(1, (short) 1)
		// .setParameter(2,
		// CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label())
		// .setParameter(3, yesTimeStart).setParameter(4, yesTimeEnd)
		// .getResultList();
		// for (CustomerBusinessFlow flow : flowYList) {
		// try {
		// customerService.operateCustomerBack(flow, flow.getCustomer(),
		// 1,projectMap);
		// } catch (Exception e) {
		// logger.debug("operateCustomerBack error " + " id " +flow.getId()+ " "
		// + e);
		// }
		// }
		// 处理当天到期的退住
		List<CustomerBusinessFlow> flowList = this.em
				.createQuery(
						"select a from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveStatus.typecode=?2 and a.leaveDate>=?3 and a.leaveDate<=?4 ")
				.setParameter(1, (short) 1).setParameter(2, CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label())
				.setParameter(3, curTimeStart).setParameter(4, curTimeEnd).getResultList();
		for (CustomerBusinessFlow flow : flowList) {
			try {
				customerService.operateCustomerBack(flow, flow.getCustomer(), 0, projectMap);
			} catch (Exception e) {
				logger.debug("operateback error " + " id " + flow.getId() + " " + e);
			}
		}
		// 服务变更列表 服务变更生效时间 到了 才处理 或者是生效时间小于今天且未处理
		logger.info("start createDayBill(back)&&operateCustomerServerChange " + CommonUtil.getCurrTimeForString());
		List<CustomerServerChange> changeList = this.em
				.createQuery(
						"select a from CustomerServerChange a where a.delStatus != ?1 and a.status.typecode=?2 and a.effectiveTime<=?3  ")
				.setParameter(1, (short) 1).setParameter(2, "applyStatus1").setParameter(3, CommonUtil.getCurrTimes())
				.getResultList();
		List<Long> cuList = new ArrayList<>();
		for (CustomerServerChange record : changeList) {
			String effectiveTime = CommonUtil.integetTimeForString(record.getEffectiveTime()).substring(0, 10);
			if (record.getCustomer().getId() != null) {
				cuList.add(record.getCustomer().getId());
				// 生效日期是昨天生效的 先更改老人服务相关信息 再生成昨天账单
				if (dayTime.substring(0, 10).equals(effectiveTime)) {
					customerService.operateCustomerServerChange(record);
					Integer timeAddBillStart = record.getEffectiveTime();
					try {
						accountService.createDayBill(record.getCustomer(), dayTime, projectMap);
					} catch (Exception e) {
						logger.debug("add day bill error1 customerid " + record.getCustomer().getId() + " " + e);
					}
				}
			}
			// 生效日期是今天生效的 先生成昨天账单 再生效
			if (curTime.substring(0, 10).equals(effectiveTime)) {
				try {
					accountService.createDayBill(record.getCustomer(), dayTime, projectMap);
				} catch (Exception e) {
					logger.debug("add day bill error2 customerid " + record.getCustomer().getId() + " " + e);
				}
				try {
					customerService.operateCustomerServerChange(record);
				} catch (Exception e) {
					logger.debug("operateCustomerServerChange error id2 " + record.getId() + " " + e);
				}
			}
			// 生效日期在今日以前的 先更改老人服务相关信息 补充生成日账单
			if (!curTime.substring(0, 10).equals(effectiveTime) && !dayTime.substring(0, 10).equals(effectiveTime)) {
				try {
					customerService.operateCustomerServerChange(record);
				} catch (Exception e1) {
					logger.debug("operateCustomerServerChange error id " + record.getId() + " " + e1);
				}
				Integer timeAddBillStart = record.getEffectiveTime();
				while (timeAddBillStart < yesTimeStart) {
					String timeAddBillStartString = CommonUtil.integetTimeForString(timeAddBillStart);
					try {
						accountService.createDayBill(record.getCustomer(), timeAddBillStartString + ":00", projectMap);
					} catch (Exception e) {
						logger.debug("add day bill error3  customerid " + record.getCustomer().getId() + " " + e);
					}
					timeAddBillStart += 24 * 60 * 60;
				}

			}
		}
		// 处于试住和入住的老人列表 生成日账单
		logger.info("start createDayBill(normal) " + CommonUtil.getCurrTimeForString());
		List<Customer> customerList = null;
		StringBuilder sql = new StringBuilder(
				"select a from Customer a where a.delStatus != ?1 and ( a.customerStatus.typecode=?2 or a.customerStatus.typecode=?3 or a.customerStatus.typecode=?4 )");
		if (cuList.size() > 0) {
			sql.append(" and a.id  not in (:cuList)");
			customerList = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
					.setParameter(2, "customerStatusTry").setParameter(3, "customerStatusIn")
					.setParameter(4, "customerStatusTryWaitIn").setParameter("cuList", cuList).getResultList();
		} else {
			customerList = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
					.setParameter(2, "customerStatusTry").setParameter(3, "customerStatusIn")
					.setParameter(4, "customerStatusTryWaitIn").getResultList();
		}
		for (Customer customer : customerList) {
			try {
				accountService.createDayBill(customer, dayTime, projectMap);
			} catch (Exception e) {
				logger.debug("createDayBill error " + " id " + customer.getId() + " " + e);
			}
		}
		logger.debug("end bill ");
	}

	/**
	 * 生成客户月账单 （每月一号凌晨一点执行）
	 */
	@Scheduled(cron = "0 0 1 1 * ?")
	@Transactional
	public void createCustomerMonthBill() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		Integer startTime = null;
		Integer endTime = null;
		logger.debug(" create month bill " + CommonUtil.getCurrTimeForString());
		try {
			startTime = CommonUtil
					.strToIntegerTime(CommonUtil.getPreMonthFirstDayForString().substring(0, 10) + " 00:00:00");
			endTime = CommonUtil
					.strToIntegerTime(CommonUtil.getMonthFirstDayForString().substring(0, 10) + " 00:00:00");
		} catch (Exception e) {
			logger.debug("createCustomerMonthBill error  " + e);
		}
		// 获取试住/入住 账单类型为空或者为月收费的老人
		StringBuilder sql = new StringBuilder("select a from Customer a where a.delStatus != ?1 "
				+ " and ( a.customerStatus.typecode=?2 or a.customerStatus.typecode=?3 or a.customerStatus.typecode=?5 ) "
				+ " and ( a.chargeType is null or a.chargeType.typecode =?4 ) ");
		List<Customer> customerList = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, "customerStatusTry").setParameter(3, "customerStatusIn")
				.setParameter(5, "customerStatusTryWaitIn")
				.setParameter(4, CommonConstants.DICTTYPE.CHARGE_TYPE_Y.label()).getResultList();
		// 获取时间内请假信息
		List<CustomerLeave> leaves = new ArrayList<>();
		leaves = this.em
				.createQuery(
						"select c from CustomerLeave c where c.delStatus!=1 and c.startTime>=?1 and c.startTime<=?2 ")
				.setParameter(1, startTime).setParameter(2, endTime).getResultList();
		Map<Customer, CustomerLeave> leaveMap = (Map<Customer, CustomerLeave>) leaves.stream()
				.collect(Collectors.toMap(CustomerLeave::getCustomer, Function.identity(), (key1, key2) -> key2));
		// 获取时间内服务变更信息
		List<CustomerServerChange> changes = new ArrayList<>();
		changes = this.em
				.createQuery(
						"select c from CustomerServerChange c where c.delStatus!=1 and c.effectiveTime>=?1 and c.effectiveTime<=?2  ")
				.setParameter(1, startTime).setParameter(2, endTime).getResultList();
		Map<Customer, CustomerServerChange> changeMap = (Map<Customer, CustomerServerChange>) changes.stream().collect(
				Collectors.toMap(CustomerServerChange::getCustomer, Function.identity(), (key1, key2) -> key2));
		for (Customer customer : customerList) {
			// 统计老人当月是否有服务变更以及请假
			try {
				accountService.createCustomerBill(customer, startTime, endTime, null, changeMap, leaveMap);// 生成上月账单
			} catch (Exception e) {
				logger.debug("accountService createCustomerBill error " + e);
			}
		}
		logger.debug("end month bill ");

	}

	/**
	 *
	 * （每周日 2点执行） 复制护理计划 到第五周
	 */
	@Scheduled(cron = "0 0 2 ? * SUN ")
	@Transactional
	public void copyCusomerPlanDetail() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		logger.debug("start copyCusomerPlanDetail " + CommonUtil.getCurrTimeForString());
		List dataList = new ArrayList<>();
		List<NursingPlanDetail> detailTempList = new ArrayList<>();// 5周后护理计划
		List<NursingPlanDetail> detailList = new ArrayList<>();// 当前周护理计划
		// 当前年周
		Date dateNow = new Date();
		Integer yearNow = dateNow.getYear() + 1900;
		Integer weekIndexNow = CommonUtil.getWeekIndexFromSun(dateNow);
		// 五周后年周
		Date dateTemp = CommonUtil.getNextSaturday(dateNow, 5);
		Integer weekIndex = CommonUtil.getWeekIndexFromSun(dateTemp);
		Integer yearTab = dateTemp.getYear() + 1900;
		// 删除五周后护理计划
		StringBuilder sqlTemp = new StringBuilder(" select u from NursingPlanDetail u where u.delStatus != ?1 "
				+ " and u.customer.customerStatus is not null "
				+ " and u.customer.customerStatus.typecode!=?2 and u.weekIndex=?3 and u.yearTab=?4 ");
		detailTempList = this.em.createQuery(sqlTemp.toString()).setParameter(1, (short) 1)
				.setParameter(2, "customerStatusOut").setParameter(3, weekIndex).setParameter(4, yearTab)
				.getResultList();
		if (detailTempList != null && detailTempList.size() > 0) {
			StringBuilder delete = new StringBuilder("delete from  NursingPlanDetail c  where  c in (:datas)");
			this.em.createQuery(delete.toString()).setParameter("datas", detailTempList).executeUpdate();
		}
		// 复制当周护理计划
		StringBuilder sql = new StringBuilder(" select u from NursingPlanDetail u where u.delStatus != ?1 "
				+ " and u.customer.customerStatus is not null "
				+ " and u.customer.customerStatus.typecode!=?2 and u.weekIndex=?3 and u.yearTab=?4 ");
		detailList = this.em.createQuery(sql.toString()).setParameter(1, (short) 1).setParameter(2, "customerStatusOut")
				.setParameter(3, weekIndexNow).setParameter(4, yearNow).getResultList();
		for (NursingPlanDetail data : detailList) {
			if (data != null) {
				NursingPlanDetail detail = new NursingPlanDetail();
				detail.setDelStatus((short) 0);
				detail.setTenant(data.getCustomer().getTenant());
				detail.setCustomer(data.getCustomer());
				detail.setItem(data.getItem());// 项目
				detail.setStartTimeTab(data.getStartTimeTab());// 时间标记
				detail.setWeekTab(data.getWeekTab());// 周几
				detail.setWeekIndex(weekIndex);
				detail.setYearTab(yearTab);
				try {
					detail.setCreateDateTime(CommonUtil.getCurrTimes());
				} catch (Exception e) {
					logger.debug("copy plan_detail error :" + e);
				}
				dataList.add(detail);
			}
		}
		if (dataList.size() > 0) {
			commonService.batchInsert(dataList);
		}
	}

	/**
	 *
	 * （每日凌晨2点执行老人统计）
	 */
	@Scheduled(cron = "0 10 2 * * ?")
	public void statOldManQuery() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		String yesTime = null;// 前天日期
		Integer yesTimeStart = null;
		Integer yesTimeEnd = null;
		try {
			yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			yesTimeStart = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 00:00:00");
			yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 23:59:59");
		} catch (Exception e) {
			logger.debug("create OldMan stat error :" + e);
		}
		List<StatQuery> dataList = new ArrayList<>();
		Iterator it;
		// 老人---性别统计 条件：删除状态不为1
		logger.info("stat17：" + CommonUtil.getCurrTimeForString());
		StringBuilder sql = new StringBuilder("select t.sex_id as item ,count(*) as sum ,t.tenant_id  "
				+ "  from tnb_customer t where t.sex_id is not null and t.del_status != 1 " + "  and t.create_time>="
				+ yesTimeStart + "  and t.create_time<=" + yesTimeEnd + "  group by t.sex_id,t.tenant_id  ");
		List dataOldSexCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataOldSexCount.iterator();
		Dicttype type18 = commonService.getDicttype("statType18");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("item").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type18);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 老人---自理能力统计 条件：删除状态不为1
		logger.info("stat18：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select t.selfCareAbility_id as item ,count(*) as sum ,t.tenant_id "
				+ "  from tnb_customer t where t.selfCareAbility_id is not null and t.del_status != 1 "
				+ "  and t.create_time>=" + yesTimeStart + "  and t.create_time<=" + yesTimeEnd
				+ "  group by t.selfCareAbility_id,t.tenant_id ");
		List dataOldSelfCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataOldSelfCount.iterator();
		Dicttype type19 = commonService.getDicttype("statType19");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("item").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type19);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 老人---年龄统计 条件：删除状态不为1
		logger.info("stat19：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100') as `ages`,count(*) as `sum` ,t.tenant_id "
						+ "  from tnb_customer t where t.age is not null and  t.del_status != 1  "
						+ "  and t.create_time>=" + yesTimeStart + "  and t.create_time<=" + yesTimeEnd
						+ "  group by elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100'),t.tenant_id ");
		List dataOldAgeCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataOldAgeCount.iterator();
		Dicttype type20 = commonService.getDicttype("statType20");
		Map<String, Dicttype> ageDicttype = new HashMap<>();
		ageDicttype.put("0-70", commonService.getDicttype("statItem4"));
		ageDicttype.put("70-79", commonService.getDicttype("statItem5"));
		ageDicttype.put("80-89", commonService.getDicttype("statItem6"));
		ageDicttype.put("90-99", commonService.getDicttype("statItem7"));
		ageDicttype.put("≥100", commonService.getDicttype("statItem8"));
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			if ("0-70".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("0-70"));
			}
			if ("70-79".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("70-79"));
			}
			if ("80-89".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("80-89"));
			}
			if ("90-99".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("90-99"));
			}
			if ("> 100".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("≥100"));
			}
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult(Integer.parseInt(rece.get("sum").toString()));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type20);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		commonService.batchInsert(dataList);
		logger.debug("end");
	}

	/**
	 * （每日凌晨2点25执行咨询统计）
	 */
	@Scheduled(cron = "0 25 2 * * ?")
	public void statConsultQuery() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		String yesTime = null;
		Integer yesTimeStart = null;
		Integer yesTimeEnd = null;
		try {
			yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			yesTimeStart = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 00:00:00");
			yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 23:59:59");
		} catch (Exception e) {
			logger.debug("create consult stat error :" + e);
		}
		List<StatQuery> dataList = new ArrayList<>();
		Iterator it;
		logger.info("stat9:" + CommonUtil.getCurrTimeForString());
		// 咨询---入住偏好 条件：咨询表 咨询时间
		StringBuilder sql = new StringBuilder(
				"select c.inPreference ,c.info.tenant, count(c.inPreference) from ConsultInpreference c where c.info in"
						+ " ( select a  from ConsultInfo a where a.delStatus != ?1 and  "
						+ "   a.advDate>=?2 and a.advDate<=?3  " + " ) group by c.inPreference,c.info.tenant");
		List dataConsultInPreference = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultInPreference.iterator();
		Dicttype type13 = commonService.getDicttype("statType13");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setDelStatus((short) 0);
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type13);
			dataList.add(data);
		}
		logger.info("end 9 " + CommonUtil.getCurrTimeForString());
		// 咨询---自理能力
		sql.delete(0, sql.length());
		sql.append(" select a.customerSelfCareAbility ,a.tenant,count(a.customerSelfCareAbility) from ConsultInfo a  "
				+ " where a.delStatus != ?1 and ( a.advDate>=?2 and a.advDate<=?3 )"
				+ " group by  a.customerSelfCareAbility,a.tenant ");
		List dataConsultSelfCareAbility = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultSelfCareAbility.iterator();
		Dicttype type15 = commonService.getDicttype("statType15");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatResult(((Number) res[2]).intValue());
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setDelStatus((short) 0);
			data.setStatType(type15);
			dataList.add(data);
		}
		// 咨询---营销渠道 条件：咨询表 咨询时间 //huibati
		logger.info("stat10:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select c.advType ,c.info.tenant, count(c.advType) from ConsultAdv c where c.info in"
				+ " ( select a  from ConsultInfo a where a.delStatus != ?1 and a.advDate>=?2 and a.advDate<=?3 ) "
				+ " group by c.advType,c.info.tenant");
		List dataConsultadvWay = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultadvWay.iterator();
		Dicttype type12 = commonService.getDicttype("statType12");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type12);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 10 " + CommonUtil.getCurrTimeForString());
		// 咨询---入住意向 条件：咨询表 咨询时间
		logger.info("stat11：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.liveIntent  ,a.tenant ,count(a.liveIntent)  from ConsultInfo a where a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.liveIntent,a.tenant  ");
		List dataConsultLiveIntent = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		Dicttype type14 = commonService.getDicttype("statType14");
		it = dataConsultLiveIntent.iterator();
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type14);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 咨询---咨询次数 条件：咨询表 咨询时间
		logger.info("stat12：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select a.tenant ,count(a)  from ConsultInfo a where a.delStatus != ?1 and ( "
				+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.tenant  ");
		List dataConsultCount = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultCount.iterator();
		Dicttype countConsult = commonService.getDicttype("statItem9");
		Dicttype type11 = commonService.getDicttype("statType11");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(countConsult);
			data.setTenant((Tenant) res[0]);
			data.setStatResult(((Number) res[1]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type11);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}

		// 咨询---销售跟进次数 条件：回访表创建时间
		logger.info("stat13：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select a.tenant ,count(a)  from BackVistInfo a where a.delStatus != ?1 and ( "
				+ "a.createDateTime>=?2 and a.createDateTime<=?3 ) group by a.tenant  ");
		List dataConsultBack = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultBack.iterator();
		Dicttype countConsultBack = commonService.getDicttype("statItem10");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(countConsultBack);
			data.setTenant((Tenant) res[0]);
			data.setStatResult(((Number) res[1]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type11);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}

		// 咨询---预约参观次数 条件：咨询日期 预约时间不为空
		logger.info("stat14：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.tenant ,count(a)  from ConsultInfo a where a.appointmentDate is not null  and a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.tenant  ");
		List dataConsultVist = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = dataConsultVist.iterator();
		Dicttype countConsultVist = commonService.getDicttype("statItem11");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(countConsultVist);
			data.setTenant((Tenant) res[0]);
			data.setStatResult(((Number) res[1]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type11);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 咨询---咨询人性别
		logger.info("stat15:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.advGender  ,a.tenant ,count(a.advGender)  from ConsultInfo a where a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) and a.advGender is not null group by a.advGender,a.tenant  ");
		List dataConsultSex = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		Dicttype type21 = commonService.getDicttype("statType21");
		it = dataConsultSex.iterator();
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type21);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 咨询---咨询人老人性别
		logger.info("stat16:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.customerGender  ,a.tenant ,count(a.customerGender)  from ConsultInfo a where a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) and a.customerGender is not null group by a.customerGender,a.tenant  ");
		List dataConsultOldSex = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		Dicttype type23 = commonService.getDicttype("statType23");
		it = dataConsultOldSex.iterator();
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type23);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 咨询---咨询人关系统计
		logger.info("stat17:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.relationType  ,a.tenant ,count(a.relationType)  from ConsultInfo a where a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) and a.relationType is not null group by a.relationType,a.tenant  ");
		List dataConsultRea = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		Dicttype type22 = commonService.getDicttype("statType22");
		it = dataConsultRea.iterator();
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type22);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}

		// 咨询---自理能力
		logger.info("stat18:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.customerSelfCareAbility  ,a.tenant ,count(a.customerSelfCareAbility)  from ConsultInfo a where a.delStatus != ?1 and ( "
						+ "a.advDate>=?2 and a.advDate<=?3 ) and a.customerSelfCareAbility is not null group by a.customerSelfCareAbility,a.tenant  ");
		List dataConsultcustomerSelfCareAbility = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		Dicttype type24 = commonService.getDicttype("statType24");
		it = dataConsultcustomerSelfCareAbility.iterator();
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type24);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}

		// 咨询---咨询时间段统计
		logger.info("stat20:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"SELECT elt(INTERVAL (FROM_UNIXTIME(createDateTime,'%k'), 0, 8, 10, 12, 14,16,18,20,24),'0-8','8-10','10-12','12-14','14-16','16-18','18-20','20-24') AS ages,count(*) AS sum,tenant_id FROM tnb_consult_info"
						+ " where createDateTime>="+ yesTimeStart + " and createDateTime<="+yesTimeEnd
						+ " GROUP BY elt(INTERVAL (FROM_UNIXTIME(createDateTime,'%k'), 0, 8, 10, 12, 14,16,18,20,24),'0-8','8-10','10-12','12-14','14-16','16-18','18-20','20-24'),tenant_id;");
		List dataTimeCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataTimeCount.iterator();
		Dicttype type25 = commonService.getDicttype("statType25");
		Map<String, Dicttype> ageDicttype = new HashMap<>();
		ageDicttype.put("0-8", commonService.getDicttype("statItem13"));
		ageDicttype.put("8-10", commonService.getDicttype("statItem14"));
		ageDicttype.put("10-12", commonService.getDicttype("statItem15"));
		ageDicttype.put("12-14", commonService.getDicttype("statItem16"));
		ageDicttype.put("14-16", commonService.getDicttype("statItem17"));
		ageDicttype.put("16-18", commonService.getDicttype("statItem18"));
		ageDicttype.put("18-20", commonService.getDicttype("statItem19"));
		ageDicttype.put("20-24", commonService.getDicttype("statItem20"));
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			if ("0-8".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("0-8"));
			}
			if ("8-10".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("8-10"));
			}
			if ("10-12".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("10-12"));
			}
			if ("12-14".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("12-14"));
			}
			if ("14-16".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("14-16"));
			}
			if ("16-18".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("16-18"));
			}
			if ("18-20".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("18-20"));
			}
			if ("20-24".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("20-24"));
			}
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult(Integer.parseInt(rece.get("sum").toString()));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type25);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		commonService.batchInsert(dataList);
		logger.debug("end");

	}

	/**
	 * （每日凌晨2点40执行入住试住退住统计）
	 */
	@Scheduled(cron = "0 40 2 * * ?")
	public void statInTryBackQuery() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		String yesTime = null;// 前天日期
		Integer yesTimeStart = null;
		Integer yesTimeEnd = null;
		try {
			yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			yesTimeStart = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 00:00:00");
			yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(0, 10) + " 23:59:59");
		} catch (Exception e) {
			logger.debug("create stat error :" + e);
		}
		List<StatQuery> dataList = new ArrayList<>();
		Iterator it;
		// 入住---试住人数统计 条件： 试住/入住开始时间不为空 试住时间处于查询范围内;
		logger.info("stat1:" + CommonUtil.getCurrTimeForString());
		StringBuilder sql = new StringBuilder(
				" select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
						+ "  and experience_start_time IS NOT NULL AND checkin_start_time IS NOT  NULL "
						+ "  and experience_start_time>= " + yesTimeStart + "  and experience_start_time<= "
						+ yesTimeEnd + "  group by tenant_id ");
		List dataTryCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataTryCount.iterator();
		Dicttype type1 = commonService.getDicttype("statType1");
		Dicttype item2 = commonService.getDicttype("statItem2");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(item2);
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type1);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 1 " + CommonUtil.getCurrTimeForString());
		// 入住---入住人数统计 条件：试住时间为空 入住开始时间(最小值)处于查询范围内;
		logger.info("stat2:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
				+ "  and experience_start_time IS  NULL AND checkin_start_time IS NOT NULL "
				+ "  and checkin_start_time>= " + yesTimeStart + "  and checkin_start_time<= " + yesTimeEnd
				+ "  group by tenant_id ");
		List dataCheckInCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataCheckInCount.iterator();
		Dicttype item1 = commonService.getDicttype("statItem1");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(item1);
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type1);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 2 " + CommonUtil.getCurrTimeForString());
		// 入住---试住入住总人数统计 条件：试住/入住开始时间(最小值)处于查询范围内;
		logger.info("stat3:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append("select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
				+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
				+ "  and checkin_start_time<= " + yesTimeEnd + "  group by tenant_id ");
		List dataInSumCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataInSumCount.iterator();
		Dicttype item3 = commonService.getDicttype("statItem3");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(item3);
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type1);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 3 " + CommonUtil.getCurrTimeForString());
		// 入住---学历统计 条件：试住/入住开始时间(最小值)处于查询范围内;
		logger.info("stat4:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select educationLevel_id as item ,count(*) as sum ,tenant_id  from tnb_customer t where educationLevel_id is not null and  t.id in "
						+ "  (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
						+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
						+ "  and checkin_start_time<= " + yesTimeEnd + " ) group by educationLevel_id,tenant_id ");
		List dataConsultAgeCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataConsultAgeCount.iterator();
		Dicttype type7 = commonService.getDicttype("statType7");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("item").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type7);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 4 " + CommonUtil.getCurrTimeForString());
		// 入住---性别统计 条件：试住/入住开始时间(最小值)处于查询范围内;
		logger.info("stat5:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select sex_id as item ,count(*) as sum ,tenant_id  from tnb_customer t where sex_id is not null and t.id in "
						+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
						+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
						+ "  and checkin_start_time<= " + yesTimeEnd + " ) group by sex_id,tenant_id ");
		List dataInSexCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataInSexCount.iterator();
		Dicttype type5 = commonService.getDicttype("statType5");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("item").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type5);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 5 " + CommonUtil.getCurrTimeForString());
		// 入住---自理能力统计 条件：试住/入住开始时间(最小值)处于查询范围内;
		logger.info("stat6:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select selfCareAbility_id as item,count(*) as sum ,tenant_id  from tnb_customer t where selfCareAbility_id is not null and t.id in "
						+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
						+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
						+ "  and checkin_start_time<= " + yesTimeEnd + " ) group by selfCareAbility_id,tenant_id ");
		List dataInSelfCareCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataInSelfCareCount.iterator();
		Dicttype type4 = commonService.getDicttype("statType4");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("item").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type4);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 6 " + CommonUtil.getCurrTimeForString());
		// 入住---年龄统计 条件：试住/入住开始时间(最小值)处于查询范围内;
		logger.info("stat7:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100') as `ages`,count(*) as `sum` ,tenant_id "
						+ "from tnb_customer t where t.age is not null and t.id in "
						+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
						+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
						+ "  and checkin_start_time<= " + yesTimeEnd
						+ " ) group by elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100'),tenant_id ");
		List dataInAgeCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataInAgeCount.iterator();
		Dicttype type6 = commonService.getDicttype("statType6");
		Map<String, Dicttype> ageDicttype = new HashMap<>();
		ageDicttype.put("0-70", commonService.getDicttype("statItem4"));
		ageDicttype.put("70-79", commonService.getDicttype("statItem5"));
		ageDicttype.put("80-89", commonService.getDicttype("statItem6"));
		ageDicttype.put("90-99", commonService.getDicttype("statItem7"));
		ageDicttype.put("≥100", commonService.getDicttype("statItem8"));
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			if ("0-70".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("0-70"));
			}
			if ("70-79".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("70-79"));
			}
			if ("80-89".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("80-89"));
			}
			if ("90-99".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("90-99"));
			}
			if ("> 100".equals(rece.get("ages").toString())) {
				data.setStatItemType(ageDicttype.get("≥100"));
			}
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult(Integer.parseInt(rece.get("sum").toString()));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type6);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 7 " + CommonUtil.getCurrTimeForString());
		// 入住分析---营销渠道
		sql.delete(0, sql.length());
		sql.append("select t.advway_id ,count(*) as result ,t2.tenant_id from tnb_businessflow_adv t  "
				+ " INNER JOIN tnb_customer_businessflow t2 " + " INNER JOIN tnb_tenant t3 ON t2.tenant_id = t3.id "
				+ " WHERE  t2.id=t.flow_id AND t.flow_id in ( select id from tnb_customer_businessflow where  del_status!=1  "
				+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
				+ "  and checkin_start_time<= " + yesTimeEnd + ")  group by advway_id,tenant_id ");
		List dataAdvCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataAdvCount.iterator();
		Dicttype type3 = commonService.getDicttype("statType3");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(commonService.getDicttypeById((Long.parseLong(rece.get("advway_id").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult(Integer.parseInt(rece.get("result").toString()));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type3);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		// 入住分析---入住偏好分析 条件：试住/入住开始时间(最小值)处于查询范围内; //huibati
		logger.info("stat8:" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select t.inpreference_id ,count(*) as result ,t2.tenant_id from tnb_customer_businessflow_inpreference t  "
						+ " INNER JOIN tnb_customer_businessflow t2 "
						+ " INNER JOIN tnb_tenant t3 ON t2.tenant_id = t3.id "
						+ " WHERE  t2.id=t.businessflow_id AND t.businessflow_id in ( select id from tnb_customer_businessflow where  del_status!=1  "
						+ "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= " + yesTimeStart
						+ "  and checkin_start_time<= " + yesTimeEnd + " ) group by inpreference_id,tenant_id ");
		List dataInPreferenceCount = getJdbcTemplate().queryForList(sql.toString());
		it = dataInPreferenceCount.iterator();
		Dicttype type2 = commonService.getDicttype("statType2");
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			StatQuery data = new StatQuery();
			data.setStatDate(yesTimeStart);
			data.setStatItemType(
					commonService.getDicttypeById((Long.parseLong(rece.get("inpreference_id").toString()))));
			Tenant tenant = new Tenant();
			tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
			data.setTenant(tenant);
			data.setStatResult(Integer.parseInt(rece.get("result").toString()));
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type2);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		logger.info("end 8 " + CommonUtil.getCurrTimeForString());
		// 退住---退住人数 条件：退住日期
		logger.info("stat15：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.tenant ,count(a)  from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveDate is not null and ( "
						+ "a.leaveDate>=?2 and a.leaveDate<=?3 ) group by a.tenant  ");
		List leaveCount = this.em.createQuery(sql.toString()).setParameter(1, (short) 1).setParameter(2, yesTimeStart)
				.setParameter(3, yesTimeEnd).getResultList();
		it = leaveCount.iterator();
		Dicttype type9 = commonService.getDicttype("statType9");
		Dicttype leaveItem = commonService.getDicttype("statItem12");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setDelStatus((short) 0);
			data.setStatDate(yesTimeStart);
			data.setStatItemType(leaveItem);
			data.setTenant((Tenant) res[0]);
			data.setStatResult(((Number) res[1]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type9);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}

		// 退住---退住原因 条件：退住日期
		logger.info("stat16：" + CommonUtil.getCurrTimeForString());
		sql.delete(0, sql.length());
		sql.append(
				"select a.leaveReason  ,a.tenant ,count(a.leaveReason)  from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveDate is not null and ( "
						+ "a.leaveDate>=?2 and a.leaveDate<=?3 ) group by a.leaveReason,a.tenant  ");
		List leaveReasonCount = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd).getResultList();
		it = leaveReasonCount.iterator();
		Dicttype type10 = commonService.getDicttype("statType10");
		while (it.hasNext()) {
			Object[] res = (Object[]) it.next();
			StatQuery data = new StatQuery();
			data.setDelStatus((short) 0);
			data.setStatDate(yesTimeStart);
			data.setStatItemType((Dicttype) res[0]);
			data.setTenant((Tenant) res[1]);
			data.setStatResult(((Number) res[2]).intValue());
			data.setCreateDateTime(CommonUtil.getCurrTimes());
			data.setStatType(type10);
			data.setDelStatus((short) 0);
			dataList.add(data);
		}
		commonService.batchInsert(dataList);
		logger.debug("end");
	}

	/**
	 * 定时拉取大耳马监测数据 1分钟执行一次
	 */
	@Scheduled(cron = "0 0/1 * * * ?")
	public void getDaermaData() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		logger.debug("start pull  daerma  data " + CommonUtil.getCurrTimeForString());
		try {
			daermaBusinessProcessor.getDeviceStatus();
		} catch (ThirdPartBusiException e) {
			logger.debug("pull daerma data error :" + e);
		}
		logger.debug("end pull  daerma  data");
	}

	/**
	 * 定时 更新大耳马以及华恒 设备状态 设备时间大于当前时间90s的 就算是异常 3分钟执行一次
	 */
	@Scheduled(cron = "0 0/3 * * * ?")
	public void updateDeviceStatus() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		logger.debug("start update  hbeacon  daerma  Status");
		try {
			deviceService.updateDeviceStatus();
		} catch (Exception e) {
			logger.debug("update device status error :" + e);
		}
		logger.debug("end update  hbeacon  daerma  Status");
	}

	/**
	 * 生成hbeacon data （两小时执行）
	 */
	@Scheduled(cron = "0 0/10 * * * ? ")
	@Transactional
	public void createHbeaconData() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			try {
				logger.debug("start create hbeacon local data");
				localHbeaconService.getScannerHbeaconData();
				logger.debug("hbeacon data");
			} catch (Exception e) {
				logger.debug("timer create hbeacon local data error :" + e);
			}
		}
	}

	/**
	 * 上传hbeacon data （一分钟执行）
	 */
	// @Scheduled(cron = "0 0/1 * * * ? ")
	@Transactional
	public void pushHbeaconData() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			try {
				logger.debug("start  push hbeacon data " + CommonUtil.getCurrTimeForString());
				localHbeaconService.pushHbeaconInfo();
			} catch (Exception e) {
				logger.debug("timer push hbeacon data error :" + e);
			}
		}
	}

	/**
	 * delete hbeacon data （0点1分执行） 弃用
	 */
	// @Scheduled(cron = "0 1 0 * * ?")
	@Transactional
	public void deleteHbeaconData() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			try {
				logger.debug("delete  push hbeacon data");
				localHbeaconService.deleteHbeaconData(CommonUtil.getCurrTimes());
			} catch (Exception e) {
				logger.debug("timer delete hbeacon data  error :" + e);
			}
		}
	}

	@Scheduled(cron = "0 0 1 * * ?")
	public void sysPersonLocationReport() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		try {
			// 全天的位置统计
			DateTime date = new DateTime();
			Long generateTime = date.minusDays(1).getMillis();
			this.locationReportSrv.sysLocationReportData(generateTime);
			// 统计昨天8点到24点的进出房间的数据,昨天0点到8点的数据会在昨天八点钟单独统计
			Integer endTime = CommonUtil.getDayBegin(new Date());
			Integer startTime = endTime - 3600 * 16;
			this.locationIOReporSrv.sysIOReportData(startTime.longValue() * 1000, endTime.longValue() * 1000);
		} catch (RuntimeException e) {
			logger.error("timer sysPersonLocationReport error:", e);
		}
	}

	@Scheduled(cron = "0 45 7 * * ?")
	public void sysNightPersonLocationReport() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		try {
			logger.debug("同步0点到8点出入房间数据---" + new Date());
			// 统计今天0点到8点的进出房间的数据
			Integer startTime = CommonUtil.getDayBegin(new Date());
			Integer endTime = startTime + 3600 * 8;
			this.locationIOReporSrv.sysIOReportData(startTime.longValue() * 1000, endTime.longValue() * 1000);
		} catch (RuntimeException e) {
			logger.error("timer sysNightPersonLocationReport error:", e);
		}
	}

	@Scheduled(cron = "0 0 1 * * ?")
	public void sysCustoemrLeaveStatus() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		try {
			this.leaveService.sysLeaveStatus();
		} catch (RuntimeException e) {
			logger.error("timer sysLeaveStatus error:", e);
		}
	}

	@Scheduled(cron = "0 0 1 * * ?")
	public void sysEmployeeLocationAnalysis() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		try {
			this.locationAnalysisService.analysis((int) (System.currentTimeMillis() / 1000));
		} catch (Exception e) {
			logger.error("employeeLocationAnalysis error", e);
		}
	}

	/**
	 *
	 * （每日凌晨3点30执行考勤统计,昨天以及前天跨天排班）
	 */
	@Scheduled(cron = "0 30 3 * * ?")
	public void statStaffAttendance() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		Integer yesTimeS = 0;
		Integer yesTimeE = 0;
		try {
			yesTimeS = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 00:00:00");
			yesTimeE = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 23:59:59");
		} catch (ParseException e) {
			logger.error("pase date error:" + e);
		}
		List statData = new ArrayList<>();
		// 获取昨天所有员工位置信息
		String queryWorkTime = " select c from PersonLocation c where c.employee is not null and c.locationTime is not null and c.delStatus!=1 and c.locationTime>=?1 and c.locationTime<=?2 order by c.employee.id,c.locationTime";
		List<PersonLocation> ssList = this.em.createQuery(queryWorkTime).setParameter(1, yesTimeS)
				.setParameter(2, yesTimeE).getResultList();
		employeeService.createWorkTimeByLocation(ssList);
		logger.debug("statStaffAttendance end:" + CommonUtil.getCurrTimeForString());
	}

	/**
	 *
	 * （每日凌晨3点30执行考勤统计） 每个员工每天最早时间以及最晚时间
	 */
	// @Scheduled(cron = "0 30 3 * * ?")
	public void statStaffWorkTime() {
		Integer yesTimeS = 0;
		Integer yesTimeE = 0;
		try {
			yesTimeS = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 00:00:00");
			yesTimeE = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 23:59:59");
		} catch (ParseException e) {
			logger.error("pase date error:" + e);
		}
		List statData = new ArrayList<>();
		String queryWorkTime = " select max(location_time) max,min(location_time) min,employee_id ,tenant_id from tnb_person_location where employee_id is not null and del_status!=1 and location_time>=?1 and location_time<=?2 group by employee_id";
		List ssList = this.em.createNativeQuery(queryWorkTime).setParameter(1, yesTimeS).setParameter(2, yesTimeE)
				.getResultList();
		for (int i = 0; ssList != null && i < ssList.size(); i++) {
			Object[] res = (Object[]) ssList.get(i);
			StaffWorkTimeStat staffWorkTimeStat = new StaffWorkTimeStat();
			Integer maxTime = ((Number) res[0]).intValue();
			Integer minTime = ((Number) res[1]).intValue();
			long eid = ((Number) res[2]).intValue();
			long tid = ((Number) res[3]).intValue();
			Employee staff = new Employee();
			staff.setId(eid);
			Tenant tenant = new Tenant();
			tenant.setId(tid);
			staffWorkTimeStat.setEmployee(staff);
			staffWorkTimeStat.setTenant(tenant);
			staffWorkTimeStat.setStartWorkTime(minTime);
			staffWorkTimeStat.setEndWorkTime(maxTime);
			staffWorkTimeStat.setStatime(yesTimeS);
			staffWorkTimeStat.setCreateDateTime(CommonUtil.getCurrTimes());
			statData.add(staffWorkTimeStat);
		}
		commonService.batchInsert(statData);
	}

	/**
	 *
	 * 生成下周用餐计划
	 */
	// @Scheduled(cron = "0 0 2 ? * SUN ")
	public void createFoodPlans() {
		StringBuilder sql = new StringBuilder("select a from Customer a where a.delStatus != ?1 and ( "
				+ "a.customerStatus.typecode=?2 or a.customerStatus.typecode=?3 or a.customerStatus.typecode=?4) ");
		List<Customer> customerList = this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
				.setParameter(2, "customerStatusTry").setParameter(3, "customerStatusIn")
				.setParameter(4, "customerStatusTryWaitIn").getResultList();
		for (Customer customer : customerList) {
			nursingService.createFoodPlans(customerList, CommonUtil.getCurrTimes());
		}
	}
	
	
	/**
	 * 没十分钟执行一次验证本地服务连接
	 */
	@Scheduled(cron = "0 0/10 * * * ? ")
	public void checkLoaclServer() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			return;
		}
		List<Tenant> tenants = new ArrayList<>();
		tenants = this.em.createQuery("select c from Tenant c where c.delStatus!=1 and c.localFlag is not null").getResultList();
		Map<String, String> tenantMap = new HashMap<>();
		if(tenants!=null){
			tenantMap = (Map<String, String>) tenants.stream()
					.collect(Collectors.toMap(Tenant::getLocalFlag, Tenant::getOrgName, (key1, key2) -> key2));
		}
		SMSUtil smsUtil = new SMSUtil();
		Set<String> flags=new HashSet();
		try {
			flags = ActionContextHolder.getLocalServerFlag().keySet();
		} catch (Exception e) {
			logger.debug("checkLoaclServer error " + e);
		}
		for(String localFlag:flags){ 
		    Integer nowTime = CommonUtil.getCurrTimes();
			Integer receiveTime = ActionContextHolder.getLocalServerFlag().get(localFlag);
			//十分钟内未收到相应local发送的数据通知
			if(nowTime-receiveTime>600){
				//send消息
				List<TenantLocalNotify> tels = new ArrayList<>();
				tels = this.em.createQuery("select c from TenantLocalNotify c where c.delStatus!=1 and c.telNum is not null and c.tenant.localFlag=?1 ").setParameter(1, localFlag).getResultList();
				for (TenantLocalNotify tel : tels) {
					smsUtil.sendSMS(tel.getTelNum(), tenantMap.get(localFlag) + " 本地服务异常,", "calling");
				}
			}
		}
	}
	
	
	/**
	 * 本地服务定时发送数据 
	 */
	@Scheduled(cron = "0 0/3 * * * ? ")
	public void sendLocalServer() {
		String serverLocalFalg = properties.getProperty("serverFlag.local");
		// != false && notnull 是本地服务
		if (serverLocalFalg != null && !"false".equals(serverLocalFalg)) {
			try {
				logger.debug("start sendLocalServer");
				localHbeaconService.sendLocalServer();
				logger.debug("hbeacon data");
			} catch (Exception e) {
				logger.debug("timer sendLocalServer error :" + e);
			}
		}
	}
}
